﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for LandscapesManagement
/// </summary>
public class LandscapesManagement
{
    #region const declaration
    public const string TABLE_NAME = "Landscapes";
    public const string LANDSCAPEID = "landscapeID";
    public const string LANDSCAPENAME = "landscapeName";
    public const string RACE = "race";
    public const string LANDSCAPEDESCRIPTION = "landscapeDescription";
    #endregion


	public LandscapesManagement()
	{
	}

    public static bool AddLand(string landName, int race, string description, List<string> pathLink)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "INSERT INTO Landscapes Values(@landName,@race,@description);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@landName", landName);
        command.Parameters.AddWithValue("@race", race);
        command.Parameters.AddWithValue("@description", description);

        int n = int.Parse(command.ExecuteScalar().ToString());

        cnn.Close();

        if (n > 0)
        {
            //add images for landscape
            for(int i=0;i<pathLink.Count;i++)
                ImageManagement.AddImg(pathLink[i], Common.IMAGE_LAND, n);

            return true;
        }
        else
            return false;
    }

    public static bool EditLand(int landID, string newLandName, int race, string description)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Landscapes Set landscapeName =@newLandName, race = @race, landscapeDescription=@description WHERE landscapeID=@landID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@newLandName", newLandName);
        command.Parameters.AddWithValue("@race", race);
        command.Parameters.AddWithValue("@description", description);
        command.Parameters.AddWithValue("@landID", landID);

        int n = command.ExecuteNonQuery();

        cnn.Close();

        if (n == 1)
            return true;
        else
            return false;
    }

    public static bool DeleteLand(int landID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Landscapes WHERE landscapeID=@landID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@landID", landID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }

    public static DataSet GetAllLand()
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        //string sqlQuery = "SELECT * FROM Landscapes ORDER BY landscapeName";
        string sqlQuery = "SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY landscapeName) AS NO, L.* FROM Landscapes L) AS SUB_SQL";
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsAllLand = new DataSet();
        adapter.Fill(dsAllLand, "AllLand");

        return dsAllLand;
    }

    public static DataSet GetLandByID(int landID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);

        string sqlQuery = "SELECT * FROM Landscapes WHERE landscapeID="+landID;
        SqlDataAdapter adapter = new SqlDataAdapter(sqlQuery, cnn);

        DataSet dsGetLandByID = new DataSet();
        adapter.Fill(dsGetLandByID, "GetLandByID");

        return dsGetLandByID;
    }
}